package metadata

import (
	"fmt"
	"strings"

	"github.com/pingcap/errors"
	"github.com/romberli/das/config"
	"github.com/romberli/das/global"
	"github.com/romberli/das/internal/dependency/metadata"
	"github.com/romberli/go-util/constant"
	"github.com/romberli/go-util/middleware"
	"github.com/romberli/go-util/middleware/mysql"
	"github.com/romberli/log"
	"github.com/spf13/viper"
)

var _ metadata.MySQLServerRepo = (*MySQLServerRepo)(nil)

// MySQLServerRepo implements dependency.MySQLServerRepo interface
type MySQLServerRepo struct {
	Database middleware.Pool
}

// NewMySQLServerRepo returns metadata.MySQLServerRepo with given middleware.Pool
func NewMySQLServerRepo(db middleware.Pool) metadata.MySQLServerRepo {
	return newMySQLServerRepo(db)
}

// NewMySQLServerRepoWithGlobal returns metadata.MySQLServerRepo with global mysql pool
func NewMySQLServerRepoWithGlobal() metadata.MySQLServerRepo {
	return newMySQLServerRepo(global.DASMySQLPool)
}

// newMySQLServerRepo returns *MySQLServerRepo with given middleware.Pool
func newMySQLServerRepo(db middleware.Pool) *MySQLServerRepo {
	return &MySQLServerRepo{db}
}

// Execute implements dependency.MySQLServerRepo interface,
// it executes command with arguments on database
func (msr *MySQLServerRepo) Execute(command string, args ...interface{}) (middleware.Result, error) {
	conn, err := msr.Database.Get()
	if err != nil {
		return nil, err
	}
	defer func() {
		err = conn.Close()
		if err != nil {
			log.Errorf("metadata MySQLServerRepo.Execute(): close database connection failed.\n%+v", err)
		}
	}()

	return conn.Execute(command, args...)
}

// Transaction returns middleware.PoolConn
func (msr *MySQLServerRepo) Transaction() (middleware.Transaction, error) {
	return msr.Database.Transaction()
}

// GetAll returns all available entities
func (msr *MySQLServerRepo) GetAll() ([]metadata.MySQLServer, error) {
	sql := `
		SELECT msi.id,
		       msi.cluster_id,
		       msi.server_name,
		       msi.service_name,
		       msi.host_ip,
		       msi.port_num,
		       msi.deployment_type,
		       msi.deployment_region,
		       msi.deployment_org,
		       msi.network_zone,
		       msi.del_flag,
		       msi.create_time,
		       msi.last_update_time
		FROM t_meta_mysql_server_info msi
			INNER JOIN t_meta_mysql_cluster_info mci ON msi.cluster_id = mci.id
		WHERE msi.del_flag = 0
		  AND mci.del_flag = 0
		ORDER BY id ;
	`
	log.Debugf("metadata MySQLServerRepo.GetAll() sql:\n%s", sql)

	result, err := msr.Execute(sql)
	if err != nil {
		return nil, err
	}
	// init []metadata.MySQLServer
	mysqlServerInfoList := make([]metadata.MySQLServer, result.RowNumber())
	for i := range mysqlServerInfoList {
		mysqlServerInfoList[i] = NewEmptyMySQLServerInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(mysqlServerInfoList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}
	// init []metadata.mysqlServer
	mysqlServerList := make([]metadata.MySQLServer, result.RowNumber())
	for i := range mysqlServerList {
		mysqlServerList[i] = mysqlServerInfoList[i]
	}

	return mysqlServerList, nil
}

// GetByClusterID Select returns an available mysqlServer of the given cluster id
func (msr *MySQLServerRepo) GetByClusterID(clusterID int) ([]metadata.MySQLServer, error) {
	sql := `
		SELECT msi.id,
		       msi.cluster_id,
		       msi.server_name,
		       msi.service_name,
		       msi.host_ip,
		       msi.port_num,
		       msi.deployment_type,
		       msi.deployment_region,
		       msi.deployment_org,
		       msi.network_zone,
		       msi.del_flag,
		       msi.create_time,
		       msi.last_update_time
		FROM t_meta_mysql_server_info msi
			INNER JOIN t_meta_mysql_cluster_info mci ON msi.cluster_id = mci.id
		WHERE msi.del_flag = 0
		  AND mci.del_flag = 0
		  AND mci.id = ? ;
	`
	log.Debugf("metadata MySQLServerRepo.GetByClusterID() sql:\n%s\nplaceholders: %s", sql, clusterID)

	result, err := msr.Execute(sql, clusterID)
	if err != nil {
		return nil, err
	}

	resultNum := result.RowNumber()
	mysqlServerList := make([]metadata.MySQLServer, resultNum)

	for row := 0; row < resultNum; row++ {
		mysqlServerID, err := result.GetInt(row, constant.ZeroInt)
		if err != nil {
			return nil, err
		}

		mysqlServer, err := msr.GetByID(mysqlServerID)
		if err != nil {
			return nil, err
		}

		mysqlServerList[row] = mysqlServer
	}

	return mysqlServerList, nil
}

// GetByID Select returns an available mysqlServer of the given id
func (msr *MySQLServerRepo) GetByID(id int) (metadata.MySQLServer, error) {
	sql := `
		SELECT msi.id,
		       msi.cluster_id,
		       msi.server_name,
		       msi.service_name,
		       msi.host_ip,
		       msi.port_num,
		       msi.deployment_type,
		       msi.deployment_region,
		       msi.deployment_org,
		       msi.network_zone,
		       msi.del_flag,
		       msi.create_time,
		       msi.last_update_time
		FROM t_meta_mysql_server_info msi
			INNER JOIN t_meta_mysql_cluster_info mci ON msi.cluster_id = mci.id
		WHERE msi.del_flag = 0
		  AND mci.del_flag = 0
		AND msi.id = ? ;
	`
	log.Debugf("metadata MySQLServerRepo.GetByID() sql:\n%s\nplaceholders: %s", sql, id)

	result, err := msr.Execute(sql, id)
	if err != nil {
		return nil, err
	}
	switch result.RowNumber() {
	case 0:
		return nil, errors.Errorf("metadata MySQLServerRepo.GetByID(): data does not exist, id: %d", id)
	case 1:
		mysqlServerInfo := NewEmptyMySQLServerInfoWithGlobal()
		// map to struct
		err = result.MapToStructByRowIndex(mysqlServerInfo, constant.ZeroInt, constant.DefaultMiddlewareTag)
		if err != nil {
			return nil, err
		}

		return mysqlServerInfo, nil
	default:
		return nil, errors.Errorf("metadata MySQLServerRepo.GetByID(): duplicate key exists, id: %d", id)
	}
}

// GetByHostInfo gets a mysql server with given host ip and port number
func (msr *MySQLServerRepo) GetByHostInfo(hostIP string, portNum int) (metadata.MySQLServer, error) {
	sql := `
		SELECT msi.id,
		       msi.cluster_id,
		       msi.server_name,
		       msi.service_name,
		       msi.host_ip,
		       msi.port_num,
		       msi.deployment_type,
		       msi.deployment_region,
		       msi.deployment_org,
		       msi.network_zone,
		       msi.del_flag,
		       msi.create_time,
		       msi.last_update_time
		FROM t_meta_mysql_server_info msi
			INNER JOIN t_meta_mysql_cluster_info mci ON msi.cluster_id = mci.id
		WHERE msi.del_flag = 0
		  AND mci.del_flag = 0
		  AND msi.host_ip = ?
		  AND msi.port_num = ? ;
	`
	log.Debugf("metadata MySQLServerRepo.GetByHostInfo() sql:\n%s\nplaceholders: %s, %d", sql, hostIP, portNum)

	result, err := msr.Execute(sql, hostIP, portNum)
	if err != nil {
		return nil, err
	}
	switch result.RowNumber() {
	case 0:
		return nil, errors.Errorf("metadata MySQLServerInfo.GetByHostInfo(): data does not exist, hostIP: %s, portNum: %d", hostIP, portNum)
	case 1:
		mysqlServerInfo := NewEmptyMySQLServerInfoWithGlobal()
		// map to struct
		err = result.MapToStructByRowIndex(mysqlServerInfo, constant.ZeroInt, constant.DefaultMiddlewareTag)
		if err != nil {
			return nil, err
		}

		return mysqlServerInfo, nil
	default:
		return nil, errors.Errorf("metadata MySQLServerInfo.GetByHostInfo(): duplicate key exists, hostIP: %s, portNum: %d", hostIP, portNum)
	}
}

// GetByServiceName gets a mysql server with given service name
func (msr *MySQLServerRepo) GetByServiceName(serviceName string) (metadata.MySQLServer, error) {
	sql := `
		SELECT msi.id,
		       msi.cluster_id,
		       msi.server_name,
		       msi.service_name,
		       msi.host_ip,
		       msi.port_num,
		       msi.deployment_type,
		       msi.deployment_region,
		       msi.deployment_org,
		       msi.network_zone,
		       msi.del_flag,
		       msi.create_time,
		       msi.last_update_time
		FROM t_meta_mysql_server_info msi
			INNER JOIN t_meta_mysql_cluster_info mci ON msi.cluster_id = mci.id
		WHERE msi.del_flag = 0
		  AND mci.del_flag = 0
		  AND msi.service_name = ? ;
	`
	log.Debugf("metadata MySQLServerRepo.GetByServiceName() sql:\n%s\nplaceholders: %s", sql, serviceName)

	result, err := msr.Execute(sql, serviceName)
	if err != nil {
		return nil, err
	}
	switch result.RowNumber() {
	case 0:
		return nil, errors.Errorf("metadata MySQLServerRepo.GetByServiceName(): data does not exist, serviceName: %s", serviceName)
	case 1:
		mysqlServerInfo := NewEmptyMySQLServerInfoWithGlobal()
		// map to struct
		err = result.MapToStructByRowIndex(mysqlServerInfo, constant.ZeroInt, constant.DefaultMiddlewareTag)
		if err != nil {
			return nil, err
		}

		return mysqlServerInfo, nil
	default:
		return nil, errors.Errorf("metadata MySQLServerRepo.GetByServiceName(): duplicate key exists, serviceName: %s", serviceName)
	}
}

// GetID gets the identity with given host ip and port number from the mysql
func (msr *MySQLServerRepo) GetID(hostIP string, portNum int) (int, error) {
	sql := `
		SELECT msi.id
		FROM t_meta_mysql_server_info msi
			INNER JOIN t_meta_mysql_cluster_info mci ON msi.cluster_id = mci.id
		WHERE msi.del_flag = 0
		  AND mci.del_flag = 0
		  AND host_ip = ?
		  AND port_num = ? ;
	`
	log.Debugf("metadata MySQLServerRepo.GetID() select sql:\n%s\nplaceholders: %s, %d", sql, hostIP, portNum)
	result, err := msr.Execute(sql, hostIP, portNum)
	if err != nil {
		return constant.ZeroInt, err
	}

	return result.GetInt(constant.ZeroInt, constant.ZeroInt)
}

// GetMySQLVersionLocal gets the mysql version with given host ip and port number from the mysql
func (msr *MySQLServerRepo) GetMySQLVersionLocal(hostIP string, portNum int) (string, error) {
	sql := `SELECT @@version ;`
	log.Debugf("metadata MySQLServerRepo.GetMySQLVersionLocal() select sql: %s", sql)
	// create mysql connection
	conn, err := mysql.NewConn(
		fmt.Sprintf("%s:%d", hostIP, portNum),
		constant.EmptyString,
		viper.GetString(config.DBApplicationMySQLUserKey),
		viper.GetString(config.DBApplicationMySQLPassKey),
	)
	if err != nil {
		return constant.EmptyString, err
	}
	defer func() {
		closeErr := conn.Close()
		if err != nil {
			log.Errorf("metadata MySQLServerRepo.GetMySQLVersionLocal() close mysql connection failed. error:\n%+v", closeErr)
		}
	}()
	// execute sql
	result, err := conn.Execute(sql)
	if err != nil {
		return constant.EmptyString, err
	}
	version, err := result.GetString(constant.ZeroInt, constant.ZeroInt)
	if err != nil {
		return constant.EmptyString, err
	}

	return strings.Split(version, constant.DashString)[constant.ZeroInt], nil
}

// IsMasterLocal returns if mysql server of given host ip and port number is a master node
func (msr *MySQLServerRepo) IsMasterLocal(hostIP string, portNum int) (bool, error) {
	// create connection
	conn, err := mysql.NewConn(
		fmt.Sprintf("%s:%d", hostIP, portNum),
		constant.EmptyString,
		viper.GetString(config.DBApplicationMySQLUserKey),
		viper.GetString(config.DBApplicationMySQLPassKey),
	)
	if err != nil {
		return false, err
	}
	defer func() {
		closeErr := conn.Close()
		if closeErr != nil {
			log.Errorf("metadata MySQLServerRepo.IsMasterLocal() close mysql connection failed. error:\n%+v", closeErr.Error())
		}
	}()

	return conn.IsMaster()
}

// GetMySQLClusterByID gets the mysql cluster of the given id
func (msr *MySQLServerRepo) GetMySQLClusterByID(id int) (metadata.MySQLCluster, error) {
	sql := `
		SELECT mci.id,
		       mci.cluster_name,
		       mci.middleware_cluster_id,
		       mci.monitor_system_id,
		       mci.env_id,
		       mci.del_flag,
		       mci.create_time,
		       mci.last_update_time
		FROM t_meta_mysql_cluster_info mci
		    INNER JOIN t_meta_mysql_server_info msi ON mci.id = msi.cluster_id
		WHERE mci.del_flag = 0
		  AND mci.del_flag = 0
		  AND mci.id = ? ;
	`
	log.Debugf("metadata MySQLServerRepo.GetMySQLClusterByID() sql:\n%s\nplaceholders: %d", sql, id)

	result, err := msr.Execute(sql, id)
	if err != nil {
		return nil, err
	}

	switch result.RowNumber() {
	case 0:
		return nil, errors.Errorf("metadata MySQLServerRepo.GetMySQLClusterByID(): data does not exist, id: %d", id)
	case 1:
		mysqlClusterInfo := NewEmptyMySQLClusterInfoWithGlobal()
		// map to struct
		err = result.MapToStructByRowIndex(mysqlClusterInfo, constant.ZeroInt, constant.DefaultMiddlewareTag)
		if err != nil {
			return nil, err
		}

		return mysqlClusterInfo, nil
	default:
		return nil, errors.Errorf("metadata MySQLServerRepo.GetMySQLClusterByID(): duplicate key exists, id: %d", id)
	}
}

// GetMonitorSystem gets monitor system with given mysql server id from the mysql
func (msr *MySQLServerRepo) GetMonitorSystem(id int) (metadata.MonitorSystem, error) {
	sql := `
		SELECT monsi.id,
			   monsi.system_name,
			   monsi.system_type,
			   monsi.host_ip,
			   monsi.port_num,
			   monsi.port_num_slow,
			   monsi.base_url,
			   monsi.env_id,
			   monsi.del_flag,
			   monsi.create_time,
			   monsi.last_update_time
		FROM t_meta_mysql_server_info mysi
			INNER JOIN t_meta_mysql_cluster_info mci ON mysi.cluster_id = mci.id
			INNER JOIN t_meta_monitor_system_info monsi ON mci.monitor_system_id = monsi.id
		WHERE mysi.del_flag = 0
		  AND mci.del_flag = 0
		  AND monsi.del_flag = 0
		  AND mysi.id = ?;
	`
	log.Debugf("metadata MySQLServerRepo.GetMonitorSystem() select sql: %s", sql)
	// execute
	result, err := msr.Execute(sql, id)
	if err != nil {
		return nil, err
	}
	monitorSystemInfo := NewEmptyMonitorSystemInfoWithGlobal()
	err = result.MapToStructByRowIndex(monitorSystemInfo, constant.ZeroInt, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return monitorSystemInfo, nil
}

// Create creates data with given mysqlServer in the middleware
func (msr *MySQLServerRepo) Create(mysqlServer metadata.MySQLServer) (metadata.MySQLServer, error) {
	sql := `
		INSERT INTO t_meta_mysql_server_info(
			cluster_id, server_name, service_name, host_ip, port_num, deployment_type, deployment_region, deployment_org, network_zone)
		VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?);`
	log.Debugf("metadata MySQLServerRepo.Create() insert sql: %s", sql)
	// execute
	_, err := msr.Execute(sql,
		mysqlServer.GetClusterID(),
		mysqlServer.GetServerName(),
		mysqlServer.GetServiceName(),
		mysqlServer.GetHostIP(),
		mysqlServer.GetPortNum(),
		mysqlServer.GetDeploymentType(),
		mysqlServer.GetDeploymentRegion(),
		mysqlServer.GetDeploymentOrg(),
		mysqlServer.GetNetworkZone(),
	)
	if err != nil {
		return nil, err
	}
	// get id
	id, err := msr.GetID(mysqlServer.GetHostIP(), mysqlServer.GetPortNum())
	if err != nil {
		return nil, err
	}
	// get mysqlServer
	return msr.GetByID(id)
}

// Update updates data with given mysqlServer in the middleware
func (msr *MySQLServerRepo) Update(mysqlServer metadata.MySQLServer) error {
	sql := `
		UPDATE t_meta_mysql_server_info SET 
			cluster_id = ?, server_name = ?, service_name = ?, host_ip = ?, port_num = ?,
			deployment_type = ?, deployment_region = ?, deployment_org = ?, network_zone = ?, del_flag = ?
		WHERE id = ?;`
	log.Debugf("metadata MySQLServerRepo.Update() update sql: %s", sql)
	_, err := msr.Execute(
		sql,
		mysqlServer.GetClusterID(),
		mysqlServer.GetServerName(),
		mysqlServer.GetServiceName(),
		mysqlServer.GetHostIP(),
		mysqlServer.GetPortNum(),
		mysqlServer.GetDeploymentType(),
		mysqlServer.GetDeploymentRegion(),
		mysqlServer.GetDeploymentOrg(),
		mysqlServer.GetNetworkZone(),
		mysqlServer.GetDelFlag(),
		mysqlServer.Identity(),
	)

	return err
}

// Delete deletes data in the middleware, it is recommended to use soft deletion,
// therefore use update instead of delete
func (msr *MySQLServerRepo) Delete(id int) error {
	sql := `DELETE FROM t_meta_mysql_server_info WHERE id = ?;`
	log.Debugf("metadata MySQLServerRepo.Delete() delete sql(t_meta_mysql_server_info): %s", sql)
	_, err := msr.Execute(sql, id)
	return err
}
